package com.st.emps.department.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.st.emps.department.model.DepartmentModel;
import com.st.emps.util.JDBCUtil;

public class DepartmentDao {

	Connection con = null;
	PreparedStatement ps = null;

	public String insert(DepartmentModel model) {
		String sql = "insert into department(code,name,telephone) values(?,?,?) ";

		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			
			ps.setString(1, model.getCode());
			ps.setString(2, model.getName());
			ps.setString(3, model.getTelephone());
			int rs = ps.executeUpdate();
			return rs + "";
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, ps);
		}
		return null;
	}

	public String delete(DepartmentModel model) {
		String sql = "delete from department where code = ?";
		try {
			con = JDBCUtil.getConnection();
			System.out.println(con);
			ps = con.prepareStatement(sql);
			ps.setString(1, model.getCode());
			System.out.println(sql);
			int rs = ps.executeUpdate();
			return rs + "";
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, ps);
		}
		return null;

	}

	public String update(DepartmentModel model) {
		String sql = "update department set name = ?,telephone = ? where code = ?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			System.out.println(model.getCode());
			System.out.println(model.getName());
			System.out.println(model.getTelephone());
			ps.setString(1, model.getName());
			ps.setString(2, model.getTelephone());
			ps.setString(3, model.getCode());
			int rs = ps.executeUpdate();
			return rs + "";
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, ps);
		}
		return null;

	}

	public List<DepartmentModel> select(DepartmentModel model) {
		StringBuffer sql = new StringBuffer("select code,name,telephone,(select count(1) from staff where department.code=department_id ) countS from department where 1=1 ");
		ResultSet rs = null;
		List<Object> list = new ArrayList<Object>();
		list = appendWhere(sql, model);
		List<DepartmentModel> relist = new ArrayList<DepartmentModel>();
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql.toString());
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				relist.add(new DepartmentModel(rs.getString("code"), rs.getString("name"), rs.getString("telephone"),rs.getLong("countS")));
			}
			return relist;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, ps, rs);
		}
		return null;
	}

	private List<Object> appendWhere(StringBuffer sql, DepartmentModel model) {
		List<Object> list = new ArrayList<Object>();
		String code = model.getCode();
		if (code != null && !code.trim().isEmpty()) {
			sql.append(" and code like ? ");
			list.add(code);
		}
		String name = model.getName();
		if (name != null && !name.trim().isEmpty()) {
			sql.append(" and name like ? ");
			list.add(name);
		}
		if(model.isPageOn()) {
			sql.append(" limit ?,? ");
			list.add(model.getRowStart());
			list.add(model.getPageLimit());
		}
		return list;
	}

	public int selectCount(DepartmentModel model) {
		StringBuffer sql = new StringBuffer("select count(1) from department where 1=1 ");
		List<Object> list = appendWhere(sql,model);
		return JDBCUtil.queryInt(sql,list);
	}

}
